﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading;
using Controller.Class;
using Controller.Enum;
using Model;
using Newtonsoft.Json.Linq;

namespace Controller.Helper
{
    public class Document
    {
        public static bool UpdateIsUpdateByAdmin(string docID, bool status)
        {
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                try
                {
                    tblDocument oldDocument = dataContext.tblDocuments.Single(doc => doc.DocID == docID);
                    oldDocument.isedited = status;
                    dataContext.SubmitChanges();
                    return true;
                }
                catch (Exception)
                {

                    return false;
                }

            }
        }

        public static bool UpdateDocumentDateByAdmin(string[] docID, DateTime updateDate, DocumentDateType updateDateType)
        {
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                try
                {
                    foreach (string docid in docID)
                    {
                        string docid1 = docid;
                        tblDocument oldDocument = dataContext.tblDocuments.Single(doc => doc.DocID == docid1);
                        switch (updateDateType)
                        {
                            case DocumentDateType.ApprovedDate:
                                oldDocument.ApproveDate = updateDate;
                                break;
                            case DocumentDateType.RejectDate:
                                oldDocument.RejectDate = updateDate;
                                break;
                            case DocumentDateType.ExportToSap:
                                oldDocument.ExportToSAPDate = updateDate;
                                break;
                            case DocumentDateType.ConfirmUploadToSap:
                                oldDocument.ComfirmUploadToSapDate = updateDate;
                                break;
                            case DocumentDateType.EditDate:
                                oldDocument.EditDataDate = updateDate;
                                break;
                        }
                        dataContext.SubmitChanges();
                    }

                    return true;
                }
                catch (Exception)
                {

                    return false;
                }

            }
        }
        public static string GetDocumentByUser(string username)
        {
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {

                //DCSalePriceDataContext nawaDataContext = new DCSalePriceDataContext();
                var allDocument = from document in dataContext.tblDocuments
                                  join status in dataContext.tblStatus on document.Status equals status.StatusID
                                  where document.OwnerDoc.Equals(username) && document.IsDisable != true
                                  orderby document.Created descending
                                  select new
                                  {
                                      document.UserName,
                                      document.DocID,
                                      document.Created,
                                      document.Doctype,
                                      document.Status,
                                      document.ValidON,
                                      document.ValidTO,
                                      document.Remark,
                                      document.Remark2,
                                      document.isedited,
                                      document.ApproveDate,
                                      status.StatusDescription
                                  };

                JObject k = JObject.FromObject(new
                {
                    AllDocument = allDocument
                });
                return k.ToString();
            }
        }
        public static string GetDocumentByUser(string username, string sortBy, bool issort)
        {
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {

                //DCSalePriceDataContext nawaDataContext = new DCSalePriceDataContext();
                var allDocument = from document in dataContext.tblDocuments
                                  join status in dataContext.tblStatus on document.Status equals status.StatusID
                                  where document.OwnerDoc.Equals(username) && document.IsDisable != true
                                  orderby document.Created descending
                                  select new
                                  {
                                      document.UserName,
                                      document.DocID,
                                      document.Created,
                                      document.Doctype,
                                      document.Status,
                                      document.ValidON,
                                      document.ValidTO,
                                      document.Remark,
                                      document.Remark2,
                                      document.isedited,
                                      document.ApproveDate,
                                      status.StatusDescription
                                  };
                if (!string.IsNullOrEmpty(sortBy))
                {
                    switch (sortBy)
                    {
                        case "name":
                            allDocument = allDocument.OrderBy(d => d.UserName);
                            break;
                        case "docid":
                            allDocument = allDocument.OrderBy(d => d.DocID);
                            break;
                        case "year":
                            allDocument = allDocument.OrderBy(d => d.Created);
                            break;
                        default:
                            allDocument = allDocument.OrderBy(d => d.DocID);
                            break;
                    }
                }
                JObject k = JObject.FromObject(new
                {
                    AllDocument = allDocument
                });
                return k.ToString();
            }
        }
        public static decimal? GetBath(decimal discountPrice, string creditTerm)
        {
            using (var dc = new DCSalePriceDataContext())
            {
                try
                {
                    List<WaterPipeConvert> result;
                    switch (creditTerm)
                    {
                        case "15":
                            result = dc.WaterPipeConverts.Where(i => i.PercentDisplay15 == discountPrice).ToList();
                            break;
                        case "30":
                            result = dc.WaterPipeConverts.Where(i => i.PercentDisplay30 == discountPrice).ToList();
                            break;
                        case "60":
                            result = dc.WaterPipeConverts.Where(i => i.PercentDisplay60 == discountPrice).ToList();
                            break;
                        default:
                            return discountPrice;
                    }
                    if (result.Count > 0)
                    {
                        return result[0].BathDisplay;
                    }
                    return discountPrice;
                }
                catch (Exception)
                {

                    return discountPrice;
                }
            }
        }

        public static tblDocument GetDocumentByID(string docID)
        {
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {

                //DCSalePriceDataContext nawaDataContext = new DCSalePriceDataContext();
                var allDocument = (from document in dataContext.tblDocuments
                                   where document.DocID.Equals(docID)
                                   select document).First();

                return allDocument;
            }
        }
        public static string GetDocumentByUser(string username, string doctype)
        {
            return GetDocumentByUser(username, doctype, "docid");
        }
        public static string GetDocumentByUser(string username, string doctype, string sortBy)
        {
            if (string.IsNullOrEmpty(doctype))
            {
                return GetDocumentByUser(username, sortBy, true);
            }
            else
            {
                using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
                {

                    //DCSalePriceDataContext nawaDataContext = new DCSalePriceDataContext();
                    var allDocument = from document in dataContext.tblDocuments
                                      join status in dataContext.tblStatus on document.Status equals status.StatusID
                                      where document.OwnerDoc.Equals(username) && document.IsDisable != true && document.Doctype.Equals(doctype)
                                      orderby document.Created descending
                                      select new
                                      {
                                          document.UserName,
                                          document.DocID,
                                          document.Created,
                                          document.Doctype,
                                          document.Status,
                                          document.ValidON,
                                          document.ValidTO,
                                          document.Remark,
                                          document.Remark2,
                                          document.isedited,
                                          document.ApproveDate,
                                          status.StatusDescription
                                      };
                    if (!string.IsNullOrEmpty(sortBy))
                    {
                        switch (sortBy)
                        {
                            case "name":
                                allDocument = allDocument.OrderBy(d => d.UserName);
                                break;
                            case "docid":
                                allDocument = allDocument.OrderBy(d => d.DocID);
                                break;
                            case "year":
                                allDocument = allDocument.OrderBy(d => d.Created);
                                break;
                            default:
                                allDocument = allDocument.OrderBy(d => d.DocID);
                                break;
                        }
                    }
                    JObject k = JObject.FromObject(new
                    {
                        AllDocument = allDocument
                    });
                    return k.ToString();
                }
            }
        }
        public static string GetDocumentByApproveUser(string aprroverUsername)
        {
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                //DCSalePriceDataContext nawaDataContext = new DCSalePriceDataContext();
                var allSale = from sales in dataContext.tblApprovalSaleMappings
                              where sales.ApprovalUserName.Equals(aprroverUsername)
                              select sales.SaleUsername;
                var allDocument = from document in dataContext.tblDocuments
                                  join user in dataContext.tblUsers on document.OwnerDoc equals user.UserName
                                  join status in dataContext.tblStatus on document.Status equals status.StatusID
                                  where allSale.Contains(document.OwnerDoc) && document.IsDisable != true
                                  select new
                                  {
                                      document.UserName,
                                      document.DocID,
                                      document.Created,
                                      document.Doctype,
                                      document.Status,
                                      document.ValidON,
                                      document.ValidTO,
                                      document.Remark,
                                      document.Remark2,
                                      user.msalegrp,
                                      status.StatusDescription
                                  };
                var AllSaleGrp = from sale in dataContext.tbl_msa331_pipe_salegrps
                                 select sale;

                var result = from document in allDocument.ToList()
                             join sale in AllSaleGrp.ToList() on document.msalegrp.ToLower() equals sale.msalegrp.ToLower()
                             orderby document.Created descending
                             select new
                             {
                                 document.UserName,
                                 document.DocID,
                                 document.Created,
                                 document.Doctype,
                                 document.Status,
                                 document.ValidON,
                                 document.ValidTO,
                                 document.Remark,
                                 document.Remark2,
                                 sale.msalerep_name,
                                 msalegrp_desc = sale.msalegrp
                             };

                JObject k = JObject.FromObject(new
                {
                    AllDocument = result
                });
                return k.ToString();
            }
        }

        public static string GetDocumentByAdmin()
        {
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                //DCSalePriceDataContext nawaDataContext = new DCSalePriceDataContext();

                var allDocument = from document in dataContext.tblDocuments
                                  join user in dataContext.tblUsers on document.OwnerDoc equals user.UserName
                                  join status in dataContext.tblStatus on document.Status equals status.StatusID
                                  where document.IsDisable != true
                                  orderby document.Created descending
                                  select new
                                  {
                                      document.UserName,
                                      document.DocID,
                                      document.Created,
                                      document.Doctype,
                                      document.Status,
                                      document.ValidON,
                                      document.ValidTO,
                                      document.Remark,
                                      document.Remark2,
                                      document.isedited,
                                      user.msalegrp,
                                      status.StatusDescription
                                  };
                var AllSaleGrp = from sale in dataContext.tbl_msa331_pipe_salegrps
                                 select sale;

                var result = from document in allDocument.ToList()
                             join sale in AllSaleGrp.ToList() on document.msalegrp.ToLower() equals sale.msalegrp.ToLower()
                             select new
                             {
                                 document.UserName,
                                 document.DocID,
                                 document.Created,
                                 document.Doctype,
                                 document.Status,
                                 document.ValidON,
                                 document.ValidTO,
                                 document.Remark,
                                 document.Remark2,
                                 document.isedited,
                                 sale.msalerep_name,
                                 sale.msalegrp_desc,
                                 document.StatusDescription
                             };

                JObject k = JObject.FromObject(new
                {
                    AllDocument = result
                });
                return k.ToString();
            }
        }

        public static string GetDocumentDetailByDocID(string docID)
        {
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                int lastestRound = dataContext.tblNDPrices.Where(p => p.ValidON < Document.GetDocumentByID(docID).Created).Max(n => n.NDRound);
                var allDocumentDetail = from documentdetail in dataContext.tblDocumentDatails
                                        join productcate in dataContext.viewProductCates on documentdetail.ProductID equals Convert.ToString(productcate.mprodh_grpID)
                                        join ndprice in dataContext.tblNDPriceDetails on productcate.mprodh_grpname equals ndprice.mprodh_grpname
                                        where documentdetail.DocID.Equals(docID) && documentdetail.isDisable != true && ndprice.NDRound == lastestRound
                                        orderby productcate.mprodh_sort
                                        select new
                                                   {
                                                       documentdetail.ProductID,
                                                       documentdetail.RefID,
                                                       documentdetail.discountPrice,
                                                       ddiscountprice = ndprice.discountPrice,
                                                       mprodh_desc = productcate.mprodh_grpname,
                                                       sort = productcate.mprodh_sort
                                                   };
                //var PDetail = from cust in nawaDataContext.tbl_msa130_prodhiers
                //              select cust;
                //var result3 = from all in allDocumentDetail.ToList()
                //              join detail in PDetail.ToList() on all.ProductID equals detail.mprodh
                //              select new
                //              {
                //                  all.ProductID,
                //                  all.RefID,
                //                  all.discountPrice,
                //                  all.ddiscountprice,
                //                  detail.mprodh_desc
                //              };


                var result1 = allDocumentDetail.Pivot(doc => doc.ProductID, doc2 => doc2.RefID, doc3 => doc3.ddiscountprice, doc4 => doc4.mprodh_desc, doc5 => doc5.sort, lst => lst.Sum(doc => doc.discountPrice));


                var result = result1.ToList();

                //var coulumn = GetColumnDocumentDocumentDetailByDocID(docID);
                JObject k = JObject.FromObject(new
                {
                    AllDocumentDetail = result1
                });
                return k.ToString();
            }
        }

        public static string GetDocumentDetailByDocID(string docID, bool isdraft)
        {
            if (!isdraft)
            {
                using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
                {
                    int lastestRound = dataContext.tblNDPrices.Where(p => p.ValidON < Document.GetDocumentByID(docID).Created).Max(n => n.NDRound);
                    var allDocumentDetail = from documentdetail in dataContext.tblDocumentDatails
                                            join productcate in dataContext.viewProductCates on documentdetail.ProductID equals Convert.ToString(productcate.mprodh_grpID)
                                            join ndprice in dataContext.tblNDPriceDetails on productcate.mprodh_grpname equals ndprice.mprodh_grpname
                                            where documentdetail.DocID.Equals(docID) && documentdetail.isDisable != true && ndprice.NDRound == lastestRound
                                            orderby productcate.mprodh_sort
                                            select new
                                            {
                                                documentdetail.ProductID,
                                                documentdetail.RefID,
                                                documentdetail.discountPrice,
                                                ddiscountprice = ndprice.discountPrice,
                                                mprodh_desc = productcate.mprodh_grpname,
                                                sort = productcate.mprodh_sort
                                            };

                    var result1 = allDocumentDetail.Pivot(doc => doc.ProductID, doc2 => doc2.RefID,
                                                          doc3 => doc3.ddiscountprice, doc4 => doc4.mprodh_desc,
                                                          doc5 => doc5.sort,
                                                          lst => lst.Sum(doc => doc.discountPrice));


                    //var coulumn = GetColumnDocumentDocumentDetailByDocID(docID);
                    JObject k = JObject.FromObject(new
                    {
                        AllDocumentDetail = result1
                    });
                    return k.ToString();
                }
            }

            else
            {
                using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
                {
                    int lastestRound = dataContext.tblNDPrices.Max(n => n.NDRound);
                    var prodJoinNDprice = from productcate in dataContext.viewProductCates
                                          join ndprice in dataContext.tblNDPriceDetails on productcate.mprodh_grpname
                                              equals ndprice.mprodh_grpname
                                          where ndprice.NDRound.Equals(lastestRound)
                                          orderby productcate.mprodh_sort
                                          select new
                                                     {
                                                         productcate.mprodh_grpID,
                                                         ddiscountprice = ndprice.discountPrice,
                                                         mprodh_desc = productcate.mprodh_grpname,
                                                         sort = productcate.mprodh_sort

                                                     };


                    var docAll = from docdetail in dataContext.tblDocumentDatails
                                 where docdetail.DocID.Equals(docID)
                                 select new
                                            {
                                                docdetail.ProductID,
                                                docdetail.RefID,
                                                docdetail.discountPrice,
                                            };

                    var allDocumentDetail = from nd in prodJoinNDprice
                                            join d in docAll on Convert.ToString(nd.mprodh_grpID) equals d.ProductID
                                            into template
                                            from ndAll in template.DefaultIfEmpty()
                                            select new
                                                       {
                                                           ProductID = ndAll.ProductID ?? "",
                                                           RefID = ndAll.RefID ?? "",
                                                           discountPrice = (decimal?)ndAll.discountPrice ?? 0,
                                                           mprodh_desc = nd.mprodh_desc ?? "",
                                                           ddiscountprice = (decimal?)nd.ddiscountprice ?? 0,
                                                           nd.sort

                                                       };
                    List<CDraftDocument> dsall = new List<CDraftDocument>();
                    foreach (var v in allDocumentDetail)
                    {
                        CDraftDocument item = new CDraftDocument();
                        item.ProductID = v.ProductID;
                        item.RefID = v.RefID;
                        item.ddiscountprice = v.ddiscountprice;
                        item.discountPrice = v.discountPrice;
                        item.mprodh_desc = v.mprodh_desc;
                        dsall.Add(item);
                    }


                    var result1 = allDocumentDetail.Pivot(doc => doc.ProductID, doc2 => doc2.RefID,
                                                          doc3 => doc3.ddiscountprice, doc4 => doc4.mprodh_desc,
                                                          doc5 => doc5.sort,
                                                          lst => lst.Sum(doc => doc.discountPrice));


                    //var coulumn = GetColumnDocumentDocumentDetailByDocID(docID);
                    JObject k = JObject.FromObject(new
                                                       {
                                                           AllDocumentDetail = result1
                                                       });
                    return k.ToString();
                }
            }
        }

        public static string GetColumnDocumentDetailByDocID(string docID)
        {
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                var nawaDataContext = new DCSalePriceDataContext();
                var allColumnDocumentDetail = (from documentdetail in dataContext.tblDocumentDatails

                                               where documentdetail.DocID.Equals(docID) && documentdetail.isDisable != true
                                               select new
                                               {
                                                   documentdetail.RefID,
                                                   documentdetail.DCID
                                               }).Distinct();
                var allColumnDocumentParent = (from documentdetailP in dataContext.tblDocumentDetailParents

                                               where documentdetailP.Docid.Equals(docID)
                                               select new
                                               {
                                                   documentdetailP.RefID,
                                                   documentdetailP.Docid,
                                                   documentdetailP.ParentText
                                               }).Distinct();
                var allColumnWithParent = (from documentdetail in allColumnDocumentDetail
                                           join p in allColumnDocumentParent on documentdetail.RefID equals p.RefID into kk
                                           from x in kk.DefaultIfEmpty()
                                           select new
                                           {
                                               documentdetail.DCID,
                                               documentdetail.RefID,
                                               x.ParentText
                                           }).Distinct();

                //var allColumnWithParent = (from documentdetail in dataContext.tblDocumentDatails
                //                           where documentdetail.DocID.Equals(docID) && documentdetail.isDisable != true
                //                          join p in dataContext.tblDocumentDetailParents
                //                              on documentdetail.RefID equals p.RefID into kk
                //                          from x in kk.DefaultIfEmpty()
                //                          select new
                //                                     {
                //                                         documentdetail.DCID,
                //                                         documentdetail.RefID,
                //                                         x.ParentText
                //                                     }).Distinct();
                var CDetail = from cust in nawaDataContext.tbl_msa120_customer_generals
                              select cust;

                var result = from c in allColumnWithParent.ToList()
                             join custDetails in CDetail.ToList() on c.RefID equals custDetails.mcustno
                             select new
                             {
                                 c.RefID,
                                 c.DCID,
                                 custDetails.mcustname1,
                                 c.ParentText
                             };

                JObject k = JObject.FromObject(new
                {
                    allColumns = result
                });
                return k.ToString();
            }
        }

        public static string GetColumnDocumentDetailByDocID(string docID, string doctype)
        {
            if (string.IsNullOrEmpty(doctype) || doctype == "01")
            {
                return GetColumnDocumentDetailByDocID(docID);
            }
            else
            {
                using (var dataContext = new DCSalePriceDataContext())
                {
                    //DCSalePriceDataContext nawaDataContext = new DCSalePriceDataContext();
                    var allColumnDocumentDetail = (from documentdetail in dataContext.tblDocumentDatails

                                                   where documentdetail.DocID.Equals(docID) && documentdetail.isDisable != true
                                                   select new
                                                   {
                                                       documentdetail.RefID,
                                                       documentdetail.DCID,
                                                       mcustname1 = documentdetail.RefID
                                                   }).Distinct();
                    var allColumnDocumentParent = (from documentdetailP in dataContext.tblDocumentDetailParents

                                                   where documentdetailP.Docid.Equals(docID) 
                                                   select new
                                                   {
                                                       documentdetailP.RefID,
                                                       documentdetailP.Docid,
                                                       documentdetailP.ParentText
                                                   }).Distinct();
                    var allColumnWithParent = (from documentdetail in allColumnDocumentDetail
                                               join p in allColumnDocumentParent on documentdetail.RefID equals p.RefID into kk
                                               from x in kk.DefaultIfEmpty()
                                               select new
                                               {
                                                   documentdetail.DCID,
                                                   documentdetail.RefID,
                                                   documentdetail.mcustname1,
                                                   x.Docid,
                                                   x.ParentText
                                               }).Distinct();
                    //var allColumnWithParent = (from documentdetail in dataContext.tblDocumentDatails
                    //                           where documentdetail.DocID.Equals(docID) && documentdetail.isDisable != true
                    //                           join p in dataContext.tblDocumentDetailParents
                    //                               on documentdetail.RefID equals p.RefID into kk
                    //                           from x in kk.DefaultIfEmpty()

                    //                           select new
                    //                           {
                    //                               documentdetail.DCID,
                    //                               documentdetail.RefID,
                    //                               mcustname1 = documentdetail.RefID,
                    //                               x.ParentText
                    //                           }).Distinct();
                    //var CDetail = from cust in nawaDataContext.tbl_msa122_customer_sale_pricegroups
                    //              select cust;

                    //var result = from c in allColumnDocumentDetail.ToList()
                    //             join custDetails in CDetail.ToList() on c.RefID equals custDetails.mpricegrp
                    //             select new
                    //                        {
                    //                            c.RefID,
                    //                            mcustname1 = custDetails.mpricegrp_desc
                    //                        };

                    JObject k = JObject.FromObject(new
                                                       {
                                                           allColumns = allColumnWithParent
                                                       });
                    return k.ToString();
                }
            }
        }
        //public static string GetColumnDocumentDetailByDocIDPriceGrp(string docID)
        //{
        //    using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
        //    {
        //        DCSalePriceDataContext nawaDataContext = new DCSalePriceDataContext();
        //        var allColumnDocumentDetail = (from documentdetail in dataContext.tblDocumentDatails

        //                                       where documentdetail.DocID.Equals(docID) && documentdetail.isDisable != true
        //                                       select new
        //                                       {
        //                                           documentdetail.RefID
        //                                       }).Distinct();

        //        var CDetail = from cust in nawaDataContext.bl_msa122_customer_sale_pricegroups
        //                      select cust;

        //        var result = from c in allColumnDocumentDetail.ToList()
        //                     join custDetails in CDetail.ToList() on c.RefID equals custDetails.mpricegrp
        //                     select new
        //                     {
        //                         c.RefID,
        //                         custDetails.mpricegrp_desc
        //                     };

        //        JObject k = JObject.FromObject(new
        //        {
        //            allColumns = result
        //        });
        //        return k.ToString();
        //    }
        //}

        public static int DocumentAction(List<tblDocument> documents, EnumAction action)
        {
            if (documents != null && documents.Count > 0)
            {
                using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
                {
                    switch (action)
                    {
                        case EnumAction.Update:
                            try
                            {
                                foreach (tblDocument newdoucment in documents)
                                {
                                    tblDocument oldDocument =
                                        dataContext.tblDocuments.Single(doc => doc.DocID == newdoucment.DocID);
                                    if (!string.IsNullOrEmpty(newdoucment.Doctype))
                                    {
                                        oldDocument.Doctype = newdoucment.Doctype;
                                    }
                                    if (!string.IsNullOrEmpty(newdoucment.Status))
                                    {
                                        oldDocument.Status = newdoucment.Status;
                                    }
                                    oldDocument.IsDisable = newdoucment.IsDisable;
                                    if (!string.IsNullOrEmpty(newdoucment.Common))
                                    {
                                        oldDocument.Common = newdoucment.Common;
                                    }
                                    if (newdoucment.Remark != "nochange")
                                    {
                                        oldDocument.Remark = newdoucment.Remark;
                                    }
                                    if (newdoucment.Remark2 != "nochange")
                                    {
                                        oldDocument.Remark2 = newdoucment.Remark2;
                                    }  
                                        
                                    if ((newdoucment.ApproveDate != null))
                                    {
                                        oldDocument.ApproveDate = newdoucment.ApproveDate;
                                    }
                                    if ((newdoucment.RejectDate != null))
                                    {
                                        oldDocument.RejectDate = newdoucment.RejectDate;
                                    }
                                    if ((newdoucment.ComfirmUploadToSapDate != null))
                                    {
                                        oldDocument.ComfirmUploadToSapDate = newdoucment.ComfirmUploadToSapDate;
                                    }
                                    if ((newdoucment.EditDataDate != null))
                                    {
                                        oldDocument.EditDataDate = newdoucment.EditDataDate;
                                    }
                                    if ((newdoucment.ValidON != null))
                                    {
                                        oldDocument.ValidON = newdoucment.ValidON;
                                    }
                                    if ((newdoucment.ValidTO != null))
                                    {
                                        oldDocument.ValidTO = newdoucment.ValidTO;
                                    }
                                    if ((newdoucment.ApproveBy != null))
                                    {
                                        oldDocument.ApproveBy = newdoucment.ApproveBy;
                                    }
                                    if (newdoucment.Created > DateTime.MinValue)
                                    {
                                        oldDocument.Created = newdoucment.Created;
                                    }
                                }
                                dataContext.SubmitChanges();
                                return documents.Count;
                            }
                            catch (Exception ex)
                            {

                                return -1;
                            }
                        case EnumAction.Insert:
                            try
                            {
                                //foreach (tblDocument document in documents)
                                //{
                                //    dataContext.tblDocuments.InsertAllOnSubmit();InsertOnSubmit(document);
                                //}
                                dataContext.tblDocuments.InsertAllOnSubmit(documents);
                                dataContext.SubmitChanges();
                                return documents.Count;

                            }
                            catch (Exception ex)
                            {
                                return -1;
                            }
                        case EnumAction.Delete:
                            try
                            {
                                var douc = from deletedocument in dataContext.tblDocuments
                                           where documents.Contains(deletedocument)
                                           select deletedocument;
                                //dataContext.tblUsers.DeleteByPK<tblUser>(users.AsQueryable());
                                int deletedDocument = dataContext.tblDocuments.DeleteBatch<tblDocument>(douc);
                                //dataContext.tblUsers.UpdateBatch<tblUser>(users1, p => new tblUser{ Email = p.DateCreated, 
                                //           UserName = "Chris Cavanagh" } );
                                //dataContext.tblUsers.DeleteAllOnSubmit(users);
                                //dataContext.SubmitChanges();
                                return deletedDocument;
                            }
                            catch (Exception)
                            {

                                return -1;
                            }
                    }
                }
            }
            else
            {
                return 0;
            }
            return 0;
        }

        //public static int DeleteDocDetailByDocID(string[] docsid)
        //{

        //}
        public static int DocumentDetailAction(List<tblDocumentDatail> documentdetails, List<tblDocumentDetailParent> documentdetailsParent, EnumAction action)
        {
            if (documentdetails != null && documentdetails.Count > 0)
            {
                using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
                {
                    switch (action)
                    {
                        case EnumAction.Update:
                            try
                            {
                                foreach (tblDocumentDatail newdoucmentdetail in documentdetails)
                                {
                                    tblDocumentDatail oldDocument =
                                        dataContext.tblDocumentDatails.SingleOrDefault(
                                            docdetail => docdetail.DocID == newdoucmentdetail.DocID && docdetail.RefID == newdoucmentdetail.RefID && docdetail.DCID.Trim() == newdoucmentdetail.DCID.Trim() && docdetail.ProductID.Trim() == newdoucmentdetail.ProductID.Trim());
                                    if (oldDocument != null)
                                    {
                                        if (!string.IsNullOrEmpty(newdoucmentdetail.DCID))
                                        {
                                            oldDocument.DCID = newdoucmentdetail.DCID;
                                        }
                                        if (!string.IsNullOrEmpty(newdoucmentdetail.RefID))
                                        {
                                            oldDocument.RefID = newdoucmentdetail.RefID;
                                        }
                                        if (!string.IsNullOrEmpty(newdoucmentdetail.ProductID))
                                        {
                                            oldDocument.ProductID = newdoucmentdetail.ProductID;
                                        }

                                        oldDocument.discountPrice = newdoucmentdetail.discountPrice;
                                        oldDocument.discountPriceBath = newdoucmentdetail.discountPriceBath;

                                        oldDocument.isDisable = newdoucmentdetail.isDisable;
                                    }
                                }
                                
                                foreach (var tblDocumentDetailParent in documentdetailsParent.Distinct())
                                {
                                    tblDocumentDetailParent oldDocument =
                                        dataContext.tblDocumentDetailParents.SingleOrDefault(
                                            docdetail => docdetail.Docid == tblDocumentDetailParent.Docid && docdetail.RefID == tblDocumentDetailParent.RefID );
                                    if (oldDocument != null)
                                    {
                                        oldDocument.ParentText = tblDocumentDetailParent.ParentText;
                                    }
                                    //const string compleateUpdate = "update tblDocumentDetailParent set ParentText = '{0}' where Docid = '{1}' and RefID = '{2}'";
                                    //dataContext.ExecuteCommand(compleateUpdate, tblDocumentDetailParent.ParentText, tblDocumentDetailParent.Docid, tblDocumentDetailParent.RefID);
                                }
                                dataContext.SubmitChanges();
                                return documentdetails.Count;
                            }
                            catch (Exception ex)
                            {

                                return -1;
                            }
                        case EnumAction.Insert:
                            try
                            {
                                //foreach (tblDocumentDatail documentdetail in documentdetails)
                                //{
                                //    dataContext.tblDocumentDatails.InsertOnSubmit(documentdetail);
                                //}
                                dataContext.tblDocumentDatails.InsertAllOnSubmit(documentdetails);
                                dataContext.tblDocumentDetailParents.InsertAllOnSubmit(documentdetailsParent.Distinct());
                                dataContext.SubmitChanges();
                                return documentdetails.Count;
                            }
                            catch (Exception ex)
                            {
                                return -1;
                            }
                        case EnumAction.Delete:
                            try
                            {
                                int deleteAll = 0;
                                foreach (tblDocumentDatail dds in documentdetails)
                                {
                                    tblDocumentDatail dds1 = dds;
                                    var ucdetail = from deletedocumentdetail in dataContext.tblDocumentDatails
                                                   where deletedocumentdetail.DocID == dds1.DocID
                                                   select deletedocumentdetail;
                                    deleteAll +=
                                    dataContext.tblDocumentDatails.DeleteBatch<tblDocumentDatail>(ucdetail);
                                }

                                //dataContext.tblUsers.DeleteByPK<tblUser>(users.AsQueryable());

                                //dataContext.tblUsers.UpdateBatch<tblUser>(users1, p => new tblUser{ Email = p.DateCreated, 
                                //           UserName = "Chris Cavanagh" } );
                                //dataContext.tblUsers.DeleteAllOnSubmit(users);
                                //dataContext.SubmitChanges();
                                return deleteAll;
                            }
                            catch (Exception)
                            {

                                return -1;
                            }
                    }
                }
            }
            else
            {
                return 0;
            }
            return 0;
        }
        public static int DocumentDetailAction(List<tblDocumentDatail> documentdetails, EnumAction action)
        {
            if (documentdetails != null && documentdetails.Count > 0)
            {
                using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
                {
                    switch (action)
                    {
                        case EnumAction.Update:
                            try
                            {
                                foreach (tblDocumentDatail newdoucmentdetail in documentdetails)
                                {
                                    tblDocumentDatail oldDocument =
                                        dataContext.tblDocumentDatails.SingleOrDefault(
                                            docdetail => docdetail.DocID == newdoucmentdetail.DocID && docdetail.RefID == newdoucmentdetail.RefID && docdetail.DCID.Trim() == newdoucmentdetail.DCID.Trim() && docdetail.ProductID.Trim() == newdoucmentdetail.ProductID.Trim());
                                    //oldDocument.DocID = newdoucmentdetail.DocID;
                                    if (!string.IsNullOrEmpty(newdoucmentdetail.DCID))
                                    {
                                        oldDocument.DCID = newdoucmentdetail.DCID;
                                    }
                                    if (!string.IsNullOrEmpty(newdoucmentdetail.RefID))
                                    {
                                        oldDocument.RefID = newdoucmentdetail.RefID;
                                    }
                                    if (!string.IsNullOrEmpty(newdoucmentdetail.ProductID))
                                    {
                                        oldDocument.ProductID = newdoucmentdetail.ProductID;
                                    }
                                    oldDocument.discountPrice = newdoucmentdetail.discountPrice;
                                    oldDocument.isDisable = newdoucmentdetail.isDisable;
                                }
                                dataContext.SubmitChanges();
                                return documentdetails.Count;
                            }
                            catch (Exception ex)
                            {

                                return -1;
                            }
                        case EnumAction.Insert:
                            try
                            {
                                //foreach (tblDocumentDatail documentdetail in documentdetails)
                                //{
                                //    dataContext.tblDocumentDatails.InsertOnSubmit(documentdetail);
                                //}
                                dataContext.tblDocumentDatails.InsertAllOnSubmit(documentdetails);
                                dataContext.SubmitChanges();
                                return documentdetails.Count;
                            }
                            catch (Exception ex)
                            {
                                return -1;
                            }
                        case EnumAction.Delete:
                            try
                            {
                                int deleteAll = 0;
                                foreach (tblDocumentDatail dds in documentdetails)
                                {
                                    tblDocumentDatail dds1 = dds;
                                    var ucdetail = from deletedocumentdetail in dataContext.tblDocumentDatails
                                                   where deletedocumentdetail.DocID == dds1.DocID
                                                   select deletedocumentdetail;
                                    deleteAll +=
                                    dataContext.tblDocumentDatails.DeleteBatch<tblDocumentDatail>(ucdetail);
                                }

                                //dataContext.tblUsers.DeleteByPK<tblUser>(users.AsQueryable());

                                //dataContext.tblUsers.UpdateBatch<tblUser>(users1, p => new tblUser{ Email = p.DateCreated, 
                                //           UserName = "Chris Cavanagh" } );
                                //dataContext.tblUsers.DeleteAllOnSubmit(users);
                                //dataContext.SubmitChanges();
                                return deleteAll;
                            }
                            catch (Exception)
                            {

                                return -1;
                            }
                    }
                }
            }
            else
            {
                return 0;
            }
            return 0;
        }

        public static string createDocumentID(string User, string doctype)
        {
            string stringDoctype = "CU";
            if (doctype == "02")
            {
                stringDoctype = "PG";
            }
            string dateString = DateTime.Today.ToString("ddMMyy");
            var year = DateTime.Today.Year;
            var month = DateTime.Today.Month;

            int itemIDint = getLastestDocID() + 1;
            return User + stringDoctype + dateString + itemIDint.ToString().PadLeft(6, '0');
        }

        public static int getLastestDocID()
        {
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                try
                {
                    var data = (from doc in dataContext.tblDocuments
                                select doc.autoID).Max();
                    return data;
                }
                catch (Exception)
                {

                    return 0;
                }


            }
        }

        public static string exportSAPfile(string[] docs)
        {
            var exportSapResults = new List<ExportSapResult>();
            exportSapResults.AddRange(exportSAPfileCustomer(docs));
            exportSapResults.AddRange(exportSAPfilePriceGrp(docs));
            string returnstringComplete = Newtonsoft.Json.JsonConvert.SerializeObject(exportSapResults);
            UpdateDocumentDateByAdmin(docs, DateTime.Now, DocumentDateType.ExportToSap);
            return returnstringComplete;
        }

        public static string exportSAPNDfile(int ndID)
        {
            var exportSapResults = new List<ExportSapNDResult>();
            exportSapResults.AddRange(exportSAPNDfileCustomer(ndID));
            exportSapResults.AddRange(exportSAPNDfilePriceGrp(ndID));
            string returnstringComplete = Newtonsoft.Json.JsonConvert.SerializeObject(exportSapResults);

            return returnstringComplete;
        }

        public static List<ExportSapResult> exportSAPfileCustomer(string[] docs)
        {

            string pathsave = "/TextSAP/" + DateTime.Today.ToString("dd-MM-yyyy") + "/";
            string filename = "";

            //string returnstring = "{'DocID:{0}, Link:{1}'}";
            //string returnstringComplete = "";
            var exportSapResults = new List<ExportSapResult>();
            using (var dataContext = new DCSalePriceDataContext())
            {
                var data = (from doc in dataContext.tblDocuments
                            join user in dataContext.tblUsers on doc.OwnerDoc equals user.UserName
                            join docdetail in dataContext.tblDocumentDatails on doc.DocID equals docdetail.DocID
                            join prodcate in dataContext.viewProductCates on docdetail.ProductID equals
                                Convert.ToString(prodcate.mprodh_grpID)
                            join prohier in dataContext.tbl_msa303_pipe_prodh_groupnames on prodcate.mprodh_grpID
                                equals prohier.mprodh_grpID

                            where docs.Contains(docdetail.DocID) && doc.Doctype.Equals("01")

                            select new
                            {
                                doc.DocID,
                                docdetail.DCID,
                                docdetail.RefID,
                                docdetail.discountPrice,
                                doc.ValidON,
                                doc.ValidTO,
                                prodcate.mprodh_levelno,
                                user.msalegrp,
                                mprodh = prohier.mprodh.Substring(0, Convert.ToInt32(prohier.mprodh_length))
                            }).Distinct();
                var data2 = from d in data
                            group d by d.mprodh_levelno
                                into groupdata
                                select groupdata;


                foreach (var d in data2)
                {
                    var complete = new StringBuilder();
                    filename = pathsave + d.First().msalegrp + "CU" + d.First().mprodh_levelno + ".txt";
                    foreach (var d2 in d)
                    {
                        string dateto = "";
                        string dateon = "";
                        DateTime validonTemp = d2.ValidON.Value;
                        if (d2.ValidON != null)
                        {
                            dateon = d2.ValidON < DateTime.Today ? DateTime.Today.ToString("dd.MM.yyyy") : d2.ValidON.Value.ToString("dd.MM.yyyy");
                            validonTemp = DateTime.Today;
                        }
                        if (d2.ValidTO != null)
                        {
                            dateto = d2.ValidTO < validonTemp ? validonTemp.ToString("dd.MM.yyyy") : d2.ValidTO.Value.ToString("dd.MM.yyyy");
                        }
                        string levelCompletet = string.Empty;
                        for (int i = 3; i <= Convert.ToInt32(new string(d2.mprodh_levelno, 1)); i++)
                        {
                            switch (i)
                            {
                                case 3:
                                    //if (d2.mprodh_levelno == '3')
                                    //{
                                    //    levelCompletet += string.Format("             KOMG-ZZPRODH3(01)                  {0}",
                                    //                         d2.mprodh.Substring(4, 3));
                                    //    levelCompletet += System.Environment.NewLine;
                                    //    break;
                                    //}
                                    levelCompletet += System.Environment.NewLine;
                                    levelCompletet += string.Format("             KOMG-ZZPRODH3(01)                  {0}",
                                                                    d2.mprodh.Substring(4, 3));
                                    //levelCompletet += System.Environment.NewLine;
                                    break;
                                case 4:
                                    //if (d2.mprodh_levelno == '4')
                                    //{
                                    //    levelCompletet += string.Format("             KOMG-ZZPRODH4(01)                  {0}",
                                    //                         d2.mprodh.Substring(7, 3));
                                    //    levelCompletet += System.Environment.NewLine;
                                    //    break;
                                    //}
                                    levelCompletet += System.Environment.NewLine;
                                    levelCompletet += string.Format("             KOMG-ZZPRODH4(01)                  {0}",
                                                                    d2.mprodh.Substring(7, 3));
                                    //levelCompletet += System.Environment.NewLine;
                                    break;
                                case 5:

                                    levelCompletet += System.Environment.NewLine;
                                    levelCompletet += string.Format("             KOMG-ZZPRODH5(01)                  {0}",
                                                                    d2.mprodh.Substring(10, 4));
                                    //levelCompletet += System.Environment.NewLine;
                                    break;
                            }
                        }
                        string dataItem = string.Empty;
                        string path;
                        string filepath;
                        string templateSap = string.Empty;
                        switch (Convert.ToInt32(new string(d2.mprodh_levelno, 1)))
                        {
                            case 3:
                                path = "/Template/templateCustomerL3.txt";
                                filepath = System.Web.HttpContext.Current.Server.MapPath(path);
                                templateSap = Core.GenericUtil.ReadFile(filepath);
                                dataItem = string.Format(templateSap, d2.DCID,
                                                   d2.RefID,
                                                   d2.mprodh.Substring(0, 2),
                                                   d2.mprodh.Substring(2, 2),
                                                   levelCompletet,
                                                   d2.discountPrice,
                                                   dateon,
                                                   dateto);
                                break;
                            case 4:
                                path = "/Template/templateCustomerL4.txt";
                                filepath = System.Web.HttpContext.Current.Server.MapPath(path);
                                templateSap = Core.GenericUtil.ReadFile(filepath);
                                dataItem = string.Format(templateSap, d2.DCID,
                                                   d2.RefID,
                                                   d2.mprodh.Substring(0, 2),
                                                   d2.mprodh.Substring(2, 2),
                                                   levelCompletet,
                                                   d2.discountPrice,
                                                   dateon,
                                                   dateto);
                                break;

                            case 5:
                                path = "/Template/templateCustomerL5.txt";
                                filepath = System.Web.HttpContext.Current.Server.MapPath(path);
                                templateSap = Core.GenericUtil.ReadFile(filepath);
                                dataItem = string.Format(templateSap, d2.DCID,
                                                   d2.RefID,
                                                   d2.mprodh.Substring(0, 2),
                                                   d2.mprodh.Substring(2, 2),
                                                   levelCompletet,
                                                   d2.discountPrice,
                                                   dateon,
                                                   dateto);
                                break;
                        }
                        //dataItem = string.Format(templateSap, d2.DCID,
                        //                            d2.RefID,
                        //                            d2.mprodh.Substring(0, 2),
                        //                            d2.mprodh.Substring(2, 2),
                        //                            levelCompletet,
                        //                            d2.discountPrice,
                        //                            dateto);


                        complete.Append(dataItem);

                    }
                    //complete.Append(System.Environment.NewLine);
                    complete.Append("ENDTCDE");
                    if (Core.GenericUtil.SaveFile(System.Web.HttpContext.Current.Server.MapPath(pathsave),
                                              System.Web.HttpContext.Current.Server.MapPath(filename),
                                              complete.ToString()))
                    {
                        var exportSapresult = new ExportSapResult { DociD = d.First().mprodh_levelno.ToString(), DocType = "Customer", FilePath = filename };
                        exportSapResults.Add(exportSapresult);
                        //returnstringComplete += "{'DocID':" + d.First().DocID + "," + "'Link:'" + filename + "},";
                    }
                }
                var ds = docs.Select(dUpdate => new tblDocument { DocID = dUpdate, Status = "40", Remark = "nochange" , Remark2 = "nochange"}).ToList();
                DocumentAction(ds, EnumAction.Update);
            }

            //returnstringComplete = Newtonsoft.Json.JsonConvert.SerializeObject(exportSapResults);
            //if (Core.GenericUtil.SaveFile(System.Web.HttpContext.Current.Server.MapPath(pathsave), System.Web.HttpContext.Current.Server.MapPath(filename), complete.ToString()))
            //{
            //    string  {'User':'" + user + "','Role':'" + role + "','IP':'" + ip + "'}
            //}
            return exportSapResults;
        }
        public static List<ExportSapResult> exportSAPfilePriceGrp(string[] docs)
        {

            string pathsave = "/TextSAP/" + DateTime.Today.ToString("dd-MM-yyyy") + "/";
            string filename = "";
            //string templateProLevel = "             KOMG-ZZPRODH{0}                      Z1";
            //string returnstring = "{'DocID:{0}, Link:{1}'}";
            //string returnstringComplete = "";
            var exportSapResults = new List<ExportSapResult>();
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {


                var data = (from doc in dataContext.tblDocuments
                            join user in dataContext.tblUsers on doc.OwnerDoc equals user.UserName
                            join docdetail in dataContext.tblDocumentDatails on doc.DocID equals docdetail.DocID
                            join prodcate in dataContext.viewProductCates on docdetail.ProductID equals
                                Convert.ToString(prodcate.mprodh_grpID)
                            join prohier in dataContext.tbl_msa303_pipe_prodh_groupnames on prodcate.mprodh_grpname
                                equals prohier.mprodh_grpname

                            where docs.Contains(docdetail.DocID) && doc.Doctype.Equals("02")

                            select new
                            {
                                doc.DocID,
                                docdetail.DCID,
                                docdetail.RefID,
                                docdetail.discountPrice,
                                doc.ValidON,
                                doc.ValidTO,
                                prodcate.mprodh_levelno,
                                user.msalegrp,
                                mprodh = prohier.mprodh.Substring(0, Convert.ToInt32(prohier.mprodh_length))
                            }).Distinct();
                var data2 = from d in data
                            group d by d.mprodh_levelno
                                into groupdata
                                select groupdata;


                foreach (var d in data2)
                {
                    var complete = new StringBuilder();
                    filename = pathsave + d.First().msalegrp + "PG" + d.First().mprodh_levelno + ".txt";
                    foreach (var d2 in d)
                    {
                        string dateto = "";
                        string dateon = "";
                        DateTime validonTemp = d2.ValidON.Value;
                        if (d2.ValidON != null)
                        {
                            dateon = d2.ValidON < DateTime.Today ? DateTime.Today.ToString("dd.MM.yyyy") : d2.ValidON.Value.ToString("dd.MM.yyyy");
                            validonTemp = DateTime.Today;
                        }
                        if (d2.ValidTO != null)
                        {
                            dateto = d2.ValidTO < validonTemp ? validonTemp.ToString("dd.MM.yyyy") : d2.ValidTO.Value.ToString("dd.MM.yyyy");
                        }
                        string levelCompletet = string.Empty;
                        for (int i = 3; i <= Convert.ToInt32(new string(d2.mprodh_levelno, 1)); i++)
                        {
                            switch (i)
                            {
                                case 3:
                                    //levelCompletet += System.Environment.NewLine;
                                    if (d2.mprodh_levelno == '3')
                                    {
                                        levelCompletet += string.Format("             KOMG-ZZPRODH3(01)                  {0}",
                                                             d2.mprodh.Substring(4, 3));

                                        break;
                                    }
                                    levelCompletet += string.Format("             KOMG-ZZPRODH3                      {0}",
                                                                    d2.mprodh.Substring(4, 3));
                                    //levelCompletet += System.Environment.NewLine;
                                    break;
                                case 4:
                                    levelCompletet += System.Environment.NewLine;
                                    if (d2.mprodh_levelno == '4')
                                    {
                                        levelCompletet += string.Format("             KOMG-ZZPRODH4(01)                  {0}",
                                                             d2.mprodh.Substring(7, 3));

                                        break;
                                    }
                                    levelCompletet += string.Format("             KOMG-ZZPRODH4                      {0}",
                                                                    d2.mprodh.Substring(7, 3));
                                    //levelCompletet += System.Environment.NewLine;
                                    break;
                                case 5:
                                    levelCompletet += System.Environment.NewLine;
                                    if (d2.mprodh_levelno == '5')
                                    {
                                        levelCompletet += string.Format("             KOMG-ZZPRODH5(01)                  {0}",
                                                             d2.mprodh.Substring(10, 4));
                                        //levelCompletet += System.Environment.NewLine;
                                        break;
                                    }
                                    levelCompletet += string.Format("             KOMG-ZZPRODH5                      {0}",
                                                                    d2.mprodh.Substring(10, 4));
                                    //levelCompletet += System.Environment.NewLine;
                                    break;
                            }
                        }
                        string dataItem = string.Empty;
                        string path;
                        string filepath;
                        string templateSap = string.Empty;
                        switch (Convert.ToInt32(new string(d2.mprodh_levelno, 1)))
                        {
                            case 3:
                                path = "/Template/templatePriceGrp3.txt";
                                filepath = System.Web.HttpContext.Current.Server.MapPath(path);
                                templateSap = Core.GenericUtil.ReadFile(filepath);
                                dataItem = string.Format(templateSap, d2.DCID,
                                                   d2.msalegrp,
                                                   d2.RefID,
                                                   d2.mprodh.Substring(0, 2),
                                                   d2.mprodh.Substring(2, 2),
                                                   levelCompletet,
                                                   d2.discountPrice,
                                                   dateon,
                                                   dateto);
                                break;
                            case 4:
                                path = "/Template/templatePriceGrp4.txt";
                                filepath = System.Web.HttpContext.Current.Server.MapPath(path);
                                templateSap = Core.GenericUtil.ReadFile(filepath);
                                dataItem = string.Format(templateSap, d2.DCID,
                                                   d2.msalegrp,
                                                   d2.RefID,
                                                   d2.mprodh.Substring(0, 2),
                                                   d2.mprodh.Substring(2, 2),
                                                   levelCompletet,
                                                   d2.discountPrice,
                                                   dateon,
                                                   dateto);
                                break;
                            case 5:
                                path = "/Template/templatePriceGrp5.txt";
                                filepath = System.Web.HttpContext.Current.Server.MapPath(path);
                                templateSap = Core.GenericUtil.ReadFile(filepath);
                                dataItem = string.Format(templateSap, d2.DCID,
                                                   d2.msalegrp,
                                                   d2.RefID,
                                                   d2.mprodh.Substring(0, 2),
                                                   d2.mprodh.Substring(2, 2),
                                                   levelCompletet,
                                                   d2.discountPrice,
                                                   dateon,
                                                   dateto);
                                break;
                        }


                        complete.Append(dataItem);

                    }
                    //complete.Append(System.Environment.NewLine);
                    complete.Append("ENDTCDE");
                    if (Core.GenericUtil.SaveFile(System.Web.HttpContext.Current.Server.MapPath(pathsave),
                                              System.Web.HttpContext.Current.Server.MapPath(filename),
                                              complete.ToString()))
                    {
                        var exportSapresult = new ExportSapResult { DociD = d.First().mprodh_levelno.ToString(), DocType = "PriceGrp", FilePath = filename };
                        exportSapResults.Add(exportSapresult);
                        //returnstringComplete += "{'DocID':" + d.First().DocID + "," + "'Link:'" + filename + "},";
                    }
                }
                var ds = docs.Select(dUpdate => new tblDocument { DocID = dUpdate, Status = "40", Remark = "nochange", Remark2 = "nochange" }).ToList();
                DocumentAction(ds, EnumAction.Update);
            }

            //returnstringComplete = Newtonsoft.Json.JsonConvert.SerializeObject(exportSapResults);
            //if (Core.GenericUtil.SaveFile(System.Web.HttpContext.Current.Server.MapPath(pathsave), System.Web.HttpContext.Current.Server.MapPath(filename), complete.ToString()))
            //{
            //    string  {'User':'" + user + "','Role':'" + role + "','IP':'" + ip + "'}
            //}
            return exportSapResults;
        }

        public static List<ExportSapNDResult> exportSAPNDfileCustomer(int nds)
        {
            string path = "/Template/templateCustomer.txt";
            string pathsave = "/TextSAP/" + DateTime.Today.ToString("dd-MM-yyyy") + "/";
            string filename = pathsave + "28NDCU3" + ".txt";
            string filepath = System.Web.HttpContext.Current.Server.MapPath(path);
            string templateSap = Core.GenericUtil.ReadFile(filepath);
            var exportSapResults = new List<ExportSapNDResult>();
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                var data = (from nd in dataContext.tblNDPrices
                            join ndDetails in dataContext.tblNDPriceDetails on nd.NDRound equals ndDetails.NDRound

                            join prohier in dataContext.tbl_msa303_pipe_prodh_groupnames on ndDetails.mprodh_grpname
                                equals prohier.mprodh_grpname

                            where nd.NDRound == nds

                            select new
                            {
                                nd.NDRound,
                                nd.ValidON,
                                nd.ValidTO,
                                ndDetails.mprodh_grpname,
                                ndDetails.discountPrice,
                                prohier.mprodh_levelno,
                                mprodh = prohier.mprodh.Substring(0, Convert.ToInt32(prohier.mprodh_length))
                            }).Distinct();
                var data2 = from ndCustomer in dataContext.tblNDPriceExportDCCustomers
                            select ndCustomer;

                path = "/Template/templateNDCustomerL3.txt";
                string dataItem = "";
                var complete = new StringBuilder();
                foreach (tblNDPriceExportDCCustomer tblNdPriceExportDcCustomer in data2)
                {
                    foreach (var item in data)
                    {
                        string dateto = "";
                        string dateon = "";
                        DateTime validonTemp = item.ValidON.Value;
                        if (item.ValidON != null)
                        {
                            dateon = item.ValidON < DateTime.Today ? DateTime.Today.ToString("dd.MM.yyyy") : item.ValidON.Value.ToString("dd.MM.yyyy");
                            validonTemp = DateTime.Today;
                        }
                        if (item.ValidTO != null)
                        {
                            dateto = item.ValidTO < validonTemp ? validonTemp.ToString("dd.MM.yyyy") : item.ValidTO.Value.ToString("dd.MM.yyyy");
                        }
                        filepath = System.Web.HttpContext.Current.Server.MapPath(path);
                        templateSap = Core.GenericUtil.ReadFile(filepath);
                        dataItem = string.Format(templateSap,
                                           tblNdPriceExportDcCustomer.CustomerID,
                                           item.mprodh.Substring(0, 2),
                                           item.mprodh.Substring(2, 2),
                                           item.mprodh.Substring(4, 3),
                                           item.discountPrice,
                                           dateon,
                                           dateto
                                           );
                        complete.Append(dataItem);
                    }
                }
                complete.Append("ENDTCDE");
                if (Core.GenericUtil.SaveFile(System.Web.HttpContext.Current.Server.MapPath(pathsave),
                                              System.Web.HttpContext.Current.Server.MapPath(filename),
                                              complete.ToString()))
                {
                    var exportSapresult = new ExportSapNDResult { NDRound = nds.ToString(), DocType = "ND_Customer_L3", FilePath = filename };
                    exportSapResults.Add(exportSapresult);
                    //returnstringComplete += "{'DocID':" + d.First().DocID + "," + "'Link:'" + filename + "},";
                }
            }
            return exportSapResults;
        }

        public static List<ExportSapNDResult> exportSAPNDfilePriceGrp(int nds)
        {
            string path = "/Template/templateCustomer.txt";
            string pathsave = "/TextSAP/" + DateTime.Today.ToString("dd-MM-yyyy") + "/";
            string filename1 = pathsave + "28NDPG3" + ".txt";
            string filename2 = pathsave + "28NDPG4" + ".txt";
            string filename3 = pathsave + "28NDPG5" + ".txt";
            string filepath = System.Web.HttpContext.Current.Server.MapPath(path);
            string templateSap = Core.GenericUtil.ReadFile(filepath);
            var exportSapResults = new List<ExportSapNDResult>();
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                var data = (from nd in dataContext.tblNDPrices
                            join ndDetails in dataContext.tblNDPriceDetails on nd.NDRound equals ndDetails.NDRound

                            join prohier in dataContext.tbl_msa303_pipe_prodh_groupnames on ndDetails.mprodh_grpname
                                equals prohier.mprodh_grpname

                            where nd.NDRound == nds

                            select new
                            {
                                nd.NDRound,
                                nd.ValidON,
                                nd.ValidTO,
                                ndDetails.mprodh_grpname,
                                ndDetails.discountPrice,
                                prohier.mprodh_levelno,
                                mprodh = prohier.mprodh.Substring(0, Convert.ToInt32(prohier.mprodh_length))
                            }).Distinct();
                var data2 = from ndPrice in dataContext.tblNDPriceExportDCPriceGrps
                            select ndPrice;


                string dataItem = "";
                var complete = new StringBuilder();
                var completeV2 = new StringBuilder();
                var completeV3 = new StringBuilder();
                var i = 0;
                foreach (tblNDPriceExportDCPriceGrp tblNdPriceExportDcPriceGrp in data2)
                {
                    foreach (var item in data)
                    {
                        string dateto = "";
                        string dateon = "";
                        DateTime validonTemp = item.ValidON.Value;
                        if (item.ValidON != null)
                        {
                            dateon = item.ValidON < DateTime.Today ? DateTime.Today.ToString("dd.MM.yyyy") : item.ValidON.Value.ToString("dd.MM.yyyy");
                            validonTemp = DateTime.Today;
                        }
                        if (item.ValidTO != null)
                        {
                            dateto = item.ValidTO < validonTemp ? validonTemp.ToString("dd.MM.yyyy") : item.ValidTO.Value.ToString("dd.MM.yyyy");
                        }
                        switch (item.mprodh_levelno)
                        {
                            case '3':
                                path = "/Template/templateNDPriceGrpL3.txt";
                                filepath = System.Web.HttpContext.Current.Server.MapPath(path);
                                templateSap = Core.GenericUtil.ReadFile(filepath);
                                dataItem = string.Format(templateSap,
                                                   tblNdPriceExportDcPriceGrp.DC,
                                                   tblNdPriceExportDcPriceGrp.PriceGrpID,
                                                   item.mprodh.Substring(0, 2),
                                                   item.mprodh.Substring(2, 2),
                                                   item.mprodh.Substring(4, 3),
                                                   item.discountPrice,
                                                   dateon,
                                                   dateto);
                                if (i == 0)
                                {
                                    complete.Append(@"SAPMV13A0100X
ENDTCDE      RV13A-KSCHL                        ZZD0");
                                    complete.AppendLine();
                                }
                                else
                                {
                                    complete.Append(@"SAPMV13A0100X
             RV13A-KSCHL                        ZZD0");
                                    complete.AppendLine();
                                }
                                complete.Append(dataItem);

                                break;
                            case '4':
                                path = "/Template/templateNDPriceGrpL4.txt";
                                filepath = System.Web.HttpContext.Current.Server.MapPath(path);
                                templateSap = Core.GenericUtil.ReadFile(filepath);
                                dataItem = string.Format(templateSap,
                                                   tblNdPriceExportDcPriceGrp.DC,
                                                   tblNdPriceExportDcPriceGrp.PriceGrpID,
                                                   item.mprodh.Substring(0, 2),
                                                   item.mprodh.Substring(2, 2),
                                                   item.mprodh.Substring(4, 3),
                                                   item.mprodh.Substring(7, 3),
                                                   item.discountPrice,
                                                   dateon,
                                                   dateto);
                                completeV2.Append(dataItem);
                                break;
                            case '5':
                                path = "/Template/templateNDPriceGrpL5.txt";
                                filepath = System.Web.HttpContext.Current.Server.MapPath(path);
                                templateSap = Core.GenericUtil.ReadFile(filepath);
                                dataItem = string.Format(templateSap,
                                                   tblNdPriceExportDcPriceGrp.DC,
                                                   tblNdPriceExportDcPriceGrp.PriceGrpID,
                                                   item.mprodh.Substring(0, 2),
                                                   item.mprodh.Substring(2, 2),
                                                   item.mprodh.Substring(4, 3),
                                                   item.mprodh.Substring(7, 3),
                                                   item.mprodh.Substring(10, 4),
                                                   item.discountPrice,
                                                   dateto);
                                completeV3.Append(dataItem);
                                break;

                        }
                        i++;
                    }
                }
                //complete.Insert(14,"ENDTCDE");
                completeV2.Append("ENDTCDE");
                completeV3.Append("ENDTCDE");
                if (Core.GenericUtil.SaveFile(System.Web.HttpContext.Current.Server.MapPath(pathsave),
                                              System.Web.HttpContext.Current.Server.MapPath(filename1),
                                              complete.ToString()) &&
                    Core.GenericUtil.SaveFile(System.Web.HttpContext.Current.Server.MapPath(pathsave),
                                              System.Web.HttpContext.Current.Server.MapPath(filename2),
                                              completeV2.ToString()) &&
                    Core.GenericUtil.SaveFile(System.Web.HttpContext.Current.Server.MapPath(pathsave),
                                              System.Web.HttpContext.Current.Server.MapPath(filename3),
                                              completeV3.ToString()))
                {
                    var exportSapresult = new ExportSapNDResult { NDRound = nds.ToString(), DocType = "ND_PriceGrp_L3", FilePath = filename1 };
                    var exportSapresult2 = new ExportSapNDResult { NDRound = nds.ToString(), DocType = "ND_PriceGrp_L4", FilePath = filename2 };
                    var exportSapresult3 = new ExportSapNDResult { NDRound = nds.ToString(), DocType = "ND_PriceGrp_L5", FilePath = filename3 };
                    exportSapResults.Add(exportSapresult);
                    exportSapResults.Add(exportSapresult2);
                    exportSapResults.Add(exportSapresult3);
                    //returnstringComplete += "{'DocID':" + d.First().DocID + "," + "'Link:'" + filename + "},";
                }
            }
            return exportSapResults;
        }
        public static string getDoctypeDesc(string docID)
        {
            switch (docID)
            {
                case "01":
                    return "ราคาส่วนลดรายลูกค้า";
                case "02":
                    return "ราคาส่วนลดรายกลุ่มสินค้า";

                default:
                    return "";
            }
        }
        public static List<ReportObjectDocument> getAllDocument()
        {

            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                var allDocument = (from document in dataContext.tblDocuments
                                   join status in dataContext.tblStatus on document.Status equals
                                       status.StatusID

                                   select new
                                       {
                                           document.DocID,
                                           document.Doctype,
                                           doctypeDescript = getDoctypeDesc(document.Doctype),
                                           document.UserName,
                                           Status = status.StatusDescription,
                                           document.ValidON,
                                           document.ValidTO,
                                           document.Created,
                                           document.OwnerDoc,
                                           document.ApproveDate,
                                           document.RejectDate,
                                           document.ExportToSAPDate,
                                           document.ComfirmUploadToSapDate,
                                           document.EditDataDate
                                       }).Select(group => new ReportObjectDocument
                                       {
                                           DocID = group.DocID,
                                           Doctype = group.Doctype,
                                           doctypeDescript = group.doctypeDescript,
                                           UserName = group.UserName,
                                           Status = group.Status,
                                           ValidON = group.ValidON,
                                           ValidTO = group.ValidTO,
                                           Created = group.Created,
                                           ApproveDate = group.ApproveDate,
                                           RejectDate = group.RejectDate,
                                           OwnerDoc = group.OwnerDoc,
                                           ExportToSAPDate = group.ExportToSAPDate,
                                           ComfirmUploadToSapDate = group.ComfirmUploadToSapDate,
                                           EditDataDate = group.EditDataDate
                                       }).ToList();
                return allDocument;
            }
        }

        public static List<ReportObjectDocument> getAllDocument(string doctype)
        {
            if (string.IsNullOrEmpty(doctype.Trim()))
            {
                return getAllDocument();
            }
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                var allDocument = (from document in dataContext.tblDocuments
                                   join status in dataContext.tblStatus on document.Status equals
                                       status.StatusID
                                   where document.Doctype.Equals(doctype)

                                   select new
                                   {
                                       document.DocID,
                                       document.Doctype,
                                       doctypeDescript = getDoctypeDesc(document.Doctype),
                                       document.UserName,
                                       Status = status.StatusDescription,
                                       document.ValidON,
                                       document.ValidTO,
                                       document.Created,
                                       document.OwnerDoc,
                                       document.ApproveDate,
                                       document.RejectDate,
                                       document.ExportToSAPDate,
                                       document.ComfirmUploadToSapDate,
                                       document.EditDataDate
                                   }).Select(group => new ReportObjectDocument
                                   {
                                       DocID = group.DocID,
                                       Doctype = group.Doctype,
                                       doctypeDescript = group.doctypeDescript,
                                       UserName = group.UserName,
                                       Status = group.Status,
                                       ValidON = group.ValidON,
                                       ValidTO = group.ValidTO,
                                       Created = group.Created,
                                       OwnerDoc = group.OwnerDoc,
                                       ApproveDate = group.ApproveDate,
                                       RejectDate = group.RejectDate,
                                       ExportToSAPDate = group.ExportToSAPDate,
                                       ComfirmUploadToSapDate = group.ComfirmUploadToSapDate,
                                       EditDataDate = group.EditDataDate
                                   }).ToList();
                return allDocument;
            }
        }

        public static List<ReportObjectDocument> getAllDocument(string doctype, string status)
        {
            if (string.IsNullOrEmpty(status.Trim()))
            {
                return getAllDocument(doctype);
            }

            if (string.IsNullOrEmpty(doctype.Trim()))
            {
                using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
                {
                    var allDocument = (from document in dataContext.tblDocuments
                                       join status1 in dataContext.tblStatus on document.Status equals
                                           status1.StatusID
                                       where document.Status.Equals(status)

                                       select new
                                       {
                                           document.DocID,
                                           document.Doctype,
                                           doctypeDescript = getDoctypeDesc(document.Doctype),
                                           document.UserName,
                                           Status = status1.StatusDescription,
                                           document.ValidON,
                                           document.ValidTO,
                                           document.Created,
                                           document.OwnerDoc,
                                           document.ApproveDate,
                                           document.RejectDate,
                                           document.ExportToSAPDate,
                                           document.ComfirmUploadToSapDate,
                                           document.EditDataDate
                                       }).Select(group => new ReportObjectDocument
                                       {
                                           DocID = group.DocID,
                                           Doctype = group.Doctype,
                                           doctypeDescript = group.doctypeDescript,
                                           UserName = group.UserName,
                                           Status = group.Status,
                                           ValidON = group.ValidON,
                                           ValidTO = group.ValidTO,
                                           Created = group.Created,
                                           OwnerDoc = group.OwnerDoc,
                                           ApproveDate = group.ApproveDate,
                                           RejectDate = group.RejectDate,
                                           ExportToSAPDate = group.ExportToSAPDate,
                                           ComfirmUploadToSapDate = group.ComfirmUploadToSapDate,
                                           EditDataDate = group.EditDataDate
                                       }).ToList();
                    return allDocument;
                }
            }
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                var allDocument = (from document in dataContext.tblDocuments
                                   join status2 in dataContext.tblStatus on document.Status equals
                                       status2.StatusID
                                   where document.Doctype.Equals(doctype) && document.Status.Equals(status)

                                   select new
                                   {
                                       document.DocID,
                                       document.Doctype,
                                       doctypeDescript = getDoctypeDesc(document.Doctype),
                                       document.UserName,
                                       Status = status2.StatusDescription,
                                       document.ValidON,
                                       document.ValidTO,
                                       document.Created,
                                       document.OwnerDoc,
                                       document.ApproveDate,
                                       document.RejectDate,
                                       document.ExportToSAPDate,
                                       document.ComfirmUploadToSapDate,
                                       document.EditDataDate
                                   }).Select(group => new ReportObjectDocument
                                   {
                                       DocID = group.DocID,
                                       Doctype = group.Doctype,
                                       doctypeDescript = group.doctypeDescript,
                                       UserName = group.UserName,
                                       Status = group.Status,
                                       ValidON = group.ValidON,
                                       ValidTO = group.ValidTO,
                                       Created = group.Created,
                                       OwnerDoc = group.OwnerDoc,
                                       ApproveDate = group.ApproveDate,
                                       RejectDate = group.RejectDate,
                                       ExportToSAPDate = group.ExportToSAPDate,
                                       ComfirmUploadToSapDate = group.ComfirmUploadToSapDate,
                                       EditDataDate = group.EditDataDate
                                   }).ToList();
                return allDocument;
            }
        }

        public static List<ReportObjectDocument> getAllDocument(string doctype, string status, string username)
        {
            if (string.IsNullOrEmpty(username.Trim()))
            {
                return getAllDocument(doctype, status);
            }

            if (string.IsNullOrEmpty(doctype.Trim()) && !string.IsNullOrEmpty(status.Trim()))
            {
                using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
                {
                    var allDocument = (from document in dataContext.tblDocuments
                                       join status2 in dataContext.tblStatus on document.Status equals
                                           status2.StatusID
                                       where document.Status.Equals(status) && document.OwnerDoc.Contains(username)
                                       select new
                                       {
                                           document.DocID,
                                           document.Doctype,
                                           doctypeDescript = getDoctypeDesc(document.Doctype),
                                           document.UserName,
                                           Status = status2.StatusDescription,
                                           document.ValidON,
                                           document.ValidTO,
                                           document.Created,
                                           document.OwnerDoc,
                                           document.ApproveDate,
                                           document.RejectDate,
                                           document.ExportToSAPDate,
                                           document.ComfirmUploadToSapDate,
                                           document.EditDataDate
                                       }).Select(group => new ReportObjectDocument
                                       {
                                           DocID = group.DocID,
                                           Doctype = group.Doctype,
                                           doctypeDescript = group.doctypeDescript,
                                           UserName = group.UserName,
                                           Status = group.Status,
                                           ValidON = group.ValidON,
                                           ValidTO = group.ValidTO,
                                           Created = group.Created,
                                           OwnerDoc = group.OwnerDoc,
                                           ApproveDate = group.ApproveDate,
                                           RejectDate = group.RejectDate,
                                           ExportToSAPDate = group.ExportToSAPDate,
                                           ComfirmUploadToSapDate = group.ComfirmUploadToSapDate,
                                           EditDataDate = group.EditDataDate

                                       }).ToList();
                    return allDocument;
                }
            }
            if (string.IsNullOrEmpty(status.Trim()) && !string.IsNullOrEmpty(doctype.Trim()))
            {
                using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
                {
                    var allDocument = (from document in dataContext.tblDocuments
                                       join status2 in dataContext.tblStatus on document.Status equals
                                           status2.StatusID
                                       where document.Doctype.Equals(doctype) && document.OwnerDoc.Contains(username)
                                       select new
                                       {
                                           document.DocID,
                                           document.Doctype,
                                           doctypeDescript = getDoctypeDesc(document.Doctype),
                                           document.UserName,
                                           Status = status2.StatusDescription,
                                           document.ValidON,
                                           document.ValidTO,
                                           document.Created,
                                           document.OwnerDoc,
                                           document.ApproveDate,
                                           document.RejectDate,
                                           document.ExportToSAPDate,
                                           document.ComfirmUploadToSapDate,
                                           document.EditDataDate
                                       }).Select(group => new ReportObjectDocument
                                       {
                                           DocID = group.DocID,
                                           Doctype = group.Doctype,
                                           doctypeDescript = group.doctypeDescript,
                                           UserName = group.UserName,
                                           Status = group.Status,
                                           ValidON = group.ValidON,
                                           ValidTO = group.ValidTO,
                                           Created = group.Created,
                                           OwnerDoc = group.OwnerDoc,
                                           ApproveDate = group.ApproveDate,
                                           RejectDate = group.RejectDate,
                                           ExportToSAPDate = group.ExportToSAPDate,
                                           ComfirmUploadToSapDate = group.ComfirmUploadToSapDate,
                                           EditDataDate = group.EditDataDate
                                       }).ToList();
                    return allDocument;
                }
            }

            if (string.IsNullOrEmpty(status.Trim()) && string.IsNullOrEmpty(doctype.Trim()))
            {
                using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
                {
                    var allDocument = (from document in dataContext.tblDocuments
                                       join status2 in dataContext.tblStatus on document.Status equals
                                           status2.StatusID
                                       where document.OwnerDoc.Contains(username)
                                       select new
                                       {
                                           document.DocID,
                                           document.Doctype,
                                           doctypeDescript = getDoctypeDesc(document.Doctype),
                                           document.UserName,
                                           Status = status2.StatusDescription,
                                           document.ValidON,
                                           document.ValidTO,
                                           document.Created,
                                           document.OwnerDoc,
                                           document.ApproveDate,
                                           document.RejectDate,
                                           document.ExportToSAPDate,
                                           document.ComfirmUploadToSapDate,
                                           document.EditDataDate
                                       }).Select(group => new ReportObjectDocument
                                       {
                                           DocID = group.DocID,
                                           Doctype = group.Doctype,
                                           doctypeDescript = group.doctypeDescript,
                                           UserName = group.UserName,
                                           Status = group.Status,
                                           ValidON = group.ValidON,
                                           ValidTO = group.ValidTO,
                                           Created = group.Created,
                                           OwnerDoc = group.OwnerDoc,
                                           ApproveDate = group.ApproveDate,
                                           RejectDate = group.RejectDate,
                                           ExportToSAPDate = group.ExportToSAPDate,
                                           ComfirmUploadToSapDate = group.ComfirmUploadToSapDate,
                                           EditDataDate = group.EditDataDate
                                       }).ToList();
                    return allDocument;
                }
            }
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {
                var allDocument = (from document in dataContext.tblDocuments
                                   join status2 in dataContext.tblStatus on document.Status equals
                                       status2.StatusID
                                   where document.Doctype.Equals(doctype) && document.Status.Equals(status) && document.OwnerDoc.Contains(username)
                                   select new
                                   {
                                       document.DocID,
                                       Doctype = getDoctypeDesc(document.Doctype),
                                       document.UserName,
                                       Status = status2.StatusDescription,
                                       document.ValidON,
                                       document.ValidTO,
                                       document.Created,
                                       document.OwnerDoc,
                                       document.ApproveDate,
                                       document.RejectDate,
                                       document.ExportToSAPDate,
                                       document.ComfirmUploadToSapDate,
                                       document.EditDataDate
                                   }).Select(group => new ReportObjectDocument
                                   {
                                       DocID = group.DocID,
                                       Doctype = group.Doctype,
                                       UserName = group.UserName,
                                       Status = group.Status,
                                       ValidON = group.ValidON,
                                       ValidTO = group.ValidTO,
                                       Created = group.Created,
                                       OwnerDoc = group.OwnerDoc,
                                       ApproveDate = group.ApproveDate,
                                       RejectDate = group.RejectDate,
                                       ExportToSAPDate = group.ExportToSAPDate,
                                       ComfirmUploadToSapDate = group.ComfirmUploadToSapDate,
                                       EditDataDate = group.EditDataDate
                                   }).ToList();
                return allDocument;
            }
        }
        public static List<ReportObjectDocumentDetail> GetDocumentDetailByDocIDReport(string doctype, string docID)
        {

            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {


                int lastestRound = dataContext.tblNDPrices.Where(p => p.ValidON < Document.GetDocumentByID(docID).Created).Max(n => n.NDRound);
                switch (doctype)
                {
                    case "01":
                        var allDocumentDetail = (from documentdetail in dataContext.tblDocumentDatails
                                                 join doc in dataContext.tblDocuments on documentdetail.DocID equals doc.DocID
                                                 join user in dataContext.tblUsers on doc.OwnerDoc equals user.UserName
                                                 join productcate in dataContext.viewProductCates on documentdetail.ProductID
                                                     equals Convert.ToString(productcate.mprodh_grpID)
                                                 join customer in dataContext.tbl_msa120_customer_generals on documentdetail.RefID equals customer.mcustno
                                                 join ndprice in dataContext.tblNDPriceDetails on productcate.mprodh_grpname
                                                     equals ndprice.mprodh_grpname
                                                 where
                                                     documentdetail.DocID.Equals(docID) && documentdetail.isDisable != true &&
                                                     ndprice.NDRound == lastestRound
                                                 orderby productcate.mprodh_sort
                                                 select new
                                                 {
                                                     ProductID = productcate.mprodh_grpname,
                                                     customer.mcustno,
                                                     customer.mcustname1,
                                                     doc.DocID,
                                                     doc.Created,
                                                     doc.ValidON,
                                                     doc.ValidTO,
                                                     doc.Remark,
                                                     doc.Remark2,
                                                     doc.Doctype,
                                                     doc.ComfirmUploadToSapDate,
                                                     user.FirstName,
                                                     user.Lastname,
                                                     user.msalegrp,
                                                     user.UserName,
                                                     documentdetail.discountPrice,
                                                     documentdetail.discountPriceBath,
                                                     documentdetail.DCID,
                                                     ddiscountprice = ndprice.discountPrice,
                                                     ndprice.NDApproverLevel1,
                                                     ndprice.NDApproverLevel2,
                                                     ndprice.NDApproverLevel3,
                                                     mprodh_desc = productcate.mprodh_grpname,
                                                     doc.ApproveLevel,
                                                     doc.ApproveBy,
                                                     productcate.mprodh_sort,
                                                 }).Select(group => new ReportObjectDocumentDetail
                                           {
                                               ProductID = group.ProductID,
                                               RefID = group.mcustname1,
                                               discountPrice = group.discountPrice,
                                               ddiscountprice = group.ddiscountprice,
                                               discountPriceBath = group.discountPriceBath,
                                               docID = group.DocID,
                                               RefId2 = group.mcustno,
                                               firstname = group.FirstName,
                                               lastname = group.Lastname,
                                               salegrp = group.msalegrp,
                                               created = group.Created.ToLongDateString(),
                                               doctype = group.Doctype,
                                               confirmToSapDate = group.ComfirmUploadToSapDate,
                                               validon = group.ValidON,
                                               validto = group.ValidTO,
                                               remark = group.Remark,
                                               remark2 = group.Remark2,
                                               cusID = group.mcustno,
                                               dc = group.DCID,
                                               level1 = group.NDApproverLevel1,
                                               level2 = group.NDApproverLevel2,
                                               level3 = group.NDApproverLevel3,
                                               approveLevel = group.ApproveLevel ?? 0,
                                               approveBy = Controller.Helper.Member.getFullNameByUserName(group.ApproveBy),
                                               approveByUserName = group.ApproveBy,
                                               sort = Convert.ToInt32(group.mprodh_sort),
                                               usernameOwner = group.UserName,
                                               isoText = "NS-QS-CS-F008/1/(1/12/58)",

                                           }).ToList();

                        return allDocumentDetail.ToList();
                    case "02":
                        var allDocumentDetail2 = (from documentdetail in dataContext.tblDocumentDatails
                                                  join doc in dataContext.tblDocuments on documentdetail.DocID equals doc.DocID
                                                  join user in dataContext.tblUsers on doc.OwnerDoc equals user.UserName
                                                  join productcate in dataContext.viewProductCates on documentdetail.ProductID
                                                      equals Convert.ToString(productcate.mprodh_grpID)
                                                  join pricegrop in dataContext.tbl_msa122_customer_sale_pricegroups on documentdetail.RefID equals pricegrop.mpricegrp
                                                  join ndprice in dataContext.tblNDPriceDetails on productcate.mprodh_grpname
                                                      equals ndprice.mprodh_grpname
                                                  orderby productcate.mprodh_sort
                                                  where
                                                      documentdetail.DocID.Equals(docID) && documentdetail.isDisable != true &&
                                                      ndprice.NDRound == lastestRound
                                                  select new
                                                  {
                                                      ProductID = productcate.mprodh_grpname,
                                                      pricegrop.mpricegrp,
                                                      documentdetail.discountPrice,
                                                      documentdetail.discountPriceBath,
                                                      documentdetail.DCID,
                                                      ddiscountprice = ndprice.discountPrice,
                                                      mprodh_desc = productcate.mprodh_grpname,
                                                      doc.DocID,
                                                      doc.Created,
                                                      doc.ValidON,
                                                      doc.ValidTO,
                                                      doc.Remark,
                                                      doc.Remark2,
                                                      doc.Doctype,
                                                      doc.ComfirmUploadToSapDate,
                                                      user.FirstName,
                                                      user.Lastname,
                                                      user.msalegrp,
                                                      user.UserName,
                                                      ndprice.NDApproverLevel1,
                                                      ndprice.NDApproverLevel2,
                                                      ndprice.NDApproverLevel3,
                                                      doc.ApproveLevel,
                                                      doc.ApproveBy,
                                                      productcate.mprodh_sort,
                                                  }).Select(group => new ReportObjectDocumentDetail
                                           {
                                               ProductID = group.ProductID,
                                               RefID = group.mpricegrp,
                                               RefId2 = group.mpricegrp,
                                               discountPrice = group.discountPrice,
                                               ddiscountprice = group.ddiscountprice,
                                               discountPriceBath = group.discountPriceBath,
                                               docID = group.DocID,
                                               firstname = group.FirstName,
                                               lastname = group.Lastname,
                                               salegrp = group.msalegrp,
                                               created = group.Created.ToLongDateString(),
                                               confirmToSapDate = group.ComfirmUploadToSapDate,
                                               doctype = group.Doctype,
                                               validon = group.ValidON,
                                               validto = group.ValidTO,
                                               remark = group.Remark,
                                               remark2 = group.Remark2,
                                               dc = group.DCID,
                                               level1 = group.NDApproverLevel1,
                                               level2 = group.NDApproverLevel2,
                                               level3 = group.NDApproverLevel3,
                                               approveLevel = group.ApproveLevel ?? 0,
                                               approveBy = Controller.Helper.Member.getFullNameByUserName(group.ApproveBy),
                                               approveByUserName = group.ApproveBy,
                                               sort = Convert.ToInt32(group.mprodh_sort),
                                               usernameOwner = group.UserName,
                                               isoText = "NS-QS-CS-F007/1/(1/12/58)",

                                           }).ToList();
                        return allDocumentDetail2.ToList();
                    default:
                        var allDocumentDetaildefault = (from documentdetail in dataContext.tblDocumentDatails
                                                        join doc in dataContext.tblDocuments on documentdetail.DocID equals doc.DocID
                                                        join user in dataContext.tblUsers on doc.OwnerDoc equals user.UserName
                                                        join productcate in dataContext.viewProductCates on documentdetail.ProductID
                                                            equals Convert.ToString(productcate.mprodh_grpID)
                                                        join customer in dataContext.tbl_msa120_customer_generals on documentdetail.RefID equals customer.mcustno
                                                        join ndprice in dataContext.tblNDPriceDetails on productcate.mprodh_grpname
                                                            equals ndprice.mprodh_grpname
                                                        orderby productcate.mprodh_sort
                                                        where
                                                            documentdetail.DocID.Equals(docID) && documentdetail.isDisable != true &&
                                                            ndprice.NDRound == lastestRound
                                                        select new
                                                        {
                                                            ProductID = productcate.mprodh_grpname,
                                                            customer.mcustno,
                                                            customer.mcustname1,
                                                            doc.DocID,
                                                            doc.Created,
                                                            doc.ValidON,
                                                            doc.ValidTO,
                                                            doc.Remark,
                                                            doc.Remark2,
                                                            doc.Doctype,
                                                            doc.ComfirmUploadToSapDate,
                                                            user.FirstName,
                                                            user.Lastname,
                                                            documentdetail.discountPrice,
                                                            documentdetail.discountPriceBath,
                                                            documentdetail.DCID,
                                                            ddiscountprice = ndprice.discountPrice,
                                                            mprodh_desc = productcate.mprodh_grpname,
                                                            ndprice.NDApproverLevel1,
                                                            ndprice.NDApproverLevel2,
                                                            ndprice.NDApproverLevel3,
                                                            productcate.mprodh_sort,
                                                        }).Select(group => new ReportObjectDocumentDetail
                                           {
                                               ProductID = group.ProductID,
                                               RefID = group.mcustname1,
                                               RefId2 =  group.mcustno,
                                               discountPrice = group.discountPrice,
                                               ddiscountprice = group.ddiscountprice,
                                               discountPriceBath = group.discountPriceBath,
                                               docID = group.DocID,
                                               firstname = group.FirstName,
                                               lastname = group.Lastname,
                                               created = group.Created.ToLongDateString(),
                                               confirmToSapDate = group.ComfirmUploadToSapDate,
                                               doctype = group.Doctype,
                                               validon = group.ValidON,
                                               validto = group.ValidTO,
                                               remark = group.Remark,
                                               remark2 = group.Remark2,
                                               cusID = group.mcustno,
                                               dc = group.DCID,
                                               level1 = group.NDApproverLevel1,
                                               level2 = group.NDApproverLevel2,
                                               level3 = group.NDApproverLevel3,
                                               sort = Convert.ToInt32(group.mprodh_sort),
                                               isoText = "NS-QS-CS-F008/1/(1/12/58)"
                                           }).ToList();
                        return allDocumentDetaildefault.ToList();

                }
            }
        }
        public static string GetStatus(string docid)
        {
            using (var dataContext = new DCSalePriceDataContext())
            {
                var statusDetails = (from doc in dataContext.tblDocuments
                              where doc.DocID == docid
                              join status in dataContext.tblStatus
                              on doc.Status equals status.StatusID
                              select status.StatusDescription).First();
                return statusDetails;
            }
        }
        public static string GetDocParent(string docid, string refID)
        {
            using (var dataContext = new DCSalePriceDataContext())
            {
                var allUser = from parent in dataContext.tblDocumentDetailParents
                              where parent.Docid == docid && parent.RefID == refID
                              select parent.ParentText;
                return allUser.Any() ? allUser.First() : "";
            }
        }
        public static List<DocumentRefObect> getCustByDocsID(string[] docsID)
        {
            using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
            {

                //DCSalePriceDataContext nawaDataContext = new DCSalePriceDataContext();
                var all01 = (from document in dataContext.tblDocuments
                             join docDetail in dataContext.tblDocumentDatails on document.DocID equals docDetail.DocID
                             join cusDetail in dataContext.tbl_msa120_customer_generals on docDetail.RefID equals
                                 cusDetail.mcustno
                             where document.Doctype.Equals("01") && docsID.Contains(document.DocID)
                             select new
                             {
                                 cusDetail.mcustno,
                                 cusDetail.mcustname1
                             }).Distinct();
                var all02 = (from document in dataContext.tblDocuments
                             join docDetail in dataContext.tblDocumentDatails on document.DocID equals docDetail.DocID
                             join grpcust in dataContext.tbl_msa122_customer_sales on docDetail.RefID equals grpcust.mpricegrp
                             join priceGrpDetail in dataContext.tbl_msa122_customer_sale_pricegroups on grpcust.mpricegrp equals
                              priceGrpDetail.mpricegrp
                             where document.Doctype.Equals("02") && docsID.Contains(document.DocID)
                             select new { priceGrpDetail.mpricegrp, priceGrpDetail.mpricegrp_desc }).Distinct();

                var all = all01.Select(i => new DocumentRefObect { RefID = i.mcustno, RefDisplay = i.mcustname1 });
                var all2 = all02.Select(i => new DocumentRefObect { RefID = i.mpricegrp, RefDisplay = i.mpricegrp_desc });
                return all.Union(all2).ToList();
            }
        }
        //public static List<tbl_msa120_customer_general> getCustByDocsIDPriceGrp(string[] docsID)
        //{
        //    using (DCSalePriceDataContext dataContext = new DCSalePriceDataContext())
        //    {
        //        //DCSalePriceDataContext nawaDataContext = new DCSalePriceDataContext();

        //        return all;
        //    }
        //}
        public static List<ProductDiscount> getProductDiscountByDocsID(string[] docsID, string custID)
        {
            using (var dataContext = new DCSalePriceDataContext())
            {

                //DCSalePriceDataContext nawaDataContext = new DCSalePriceDataContext();
                var all01 = (from document in dataContext.tblDocuments
                             join docDetail in dataContext.tblDocumentDatails on document.DocID equals docDetail.DocID
                             join cusDetail in dataContext.tbl_msa120_customer_generals on docDetail.RefID equals
                                 cusDetail.mcustno
                             join ProdDetail in dataContext.viewProductCates on Convert.ToInt32(docDetail.ProductID)
                                 equals ProdDetail.mprodh_grpID

                             where
                                 document.Doctype.Equals("01") && docsID.Contains(document.DocID) &&
                                 docDetail.RefID.Equals(custID)
                             select new
                                        {
                                            mprodh_grpID = Convert.ToInt32(ProdDetail.mprodh_grpID),
                                            ProdDetail.mprodh_grpname,
                                            docDetail.discountPrice,
                                            docDetail.discountPriceBath,
                                        }).Distinct();

                var all02 = (from document in dataContext.tblDocuments
                             join docDetail in dataContext.tblDocumentDatails on document.DocID equals docDetail.DocID
                             join grpcust in dataContext.tbl_msa122_customer_sales on docDetail.RefID equals grpcust.mpricegrp
                             join ProdDetail in dataContext.viewProductCates on Convert.ToInt32(docDetail.ProductID)
                           equals ProdDetail.mprodh_grpID

                             where document.Doctype.Equals("02") && docsID.Contains(document.DocID) &&
                             docDetail.RefID.Equals(custID)
                             select new
                             {
                                 mprodh_grpID = Convert.ToInt32(ProdDetail.mprodh_grpID),
                                 ProdDetail.mprodh_grpname,
                                 docDetail.discountPrice,
                                 docDetail.discountPriceBath,
                             }).Distinct();

                var all = all01.Concat(all02).ToList();

                return all.Select(i => new ProductDiscount { producGrpID = Convert.ToInt32(i.mprodh_grpID), prodDisplayName = i.mprodh_grpname, discount = i.discountPriceBath == null ? i.discountPrice : i.discountPriceBath.Value , discountBath = i.discountPriceBath ?? -1 }).ToList();
            }
        }
        public static string getCustByDocsIDJson(string[] docsID)
        {
            List<DocumentRefObect> all = getCustByDocsID(docsID);
            JObject k = JObject.FromObject(new
            {
                All = all
            });
            return k.ToString();
        }
    }


}
